--member--
drop table member;
create table member(
	member_id varchar2(100) primary key,
	member_name varchar2(100) not null,
	member_password varchar2(100) not null,
	member_nickname varchar2(100) not null,
	member_gender varchar2(10) not null,
	member_birthday date not null,
	member_email varchar2(100) not null,
	member_point number default 0,
	member_phone varchar2(100) not null,
	member_money number default 0,
	member_sellAuthority varchar2(20) default 'fail'
)
-- 기본 멤버 값 --
select * from member;
insert into member (member_id,member_name,member_password,member_nickname,member_gender,member_birthday,member_email,member_phone,member_point)
values('java','영일','1234','식신','남자','1988/01/03','yyi0563@email.com','010-2510-0563',-1);
insert into member (member_id,member_name,member_password,member_nickname,member_gender,member_birthday,member_email,member_phone,member_point)
values('jdbc','지수','1234','절세미녀','여자','1988/01/03',' aaa@email.com','010-1111-1111',-1);

insert into member (member_id,member_name,member_password,member_nickname,member_gender,member_birthday,member_email,member_phone,member_point)
values('spring','박배충','1234','식신','남자','1988/01/03','yyi0563@email.com','010-2510-0563',-1);



--jjoms_grade--
create table jjoms_grade(
grade varchar2(100) primary key,
lowpoint number not null,
hipoint number not null
)
drop table jjoms_grade

-- 필요한 등급 값 --
insert into jjoms_grade (grade,lowpoint,hipoint) values('관리자',-1,-1);
insert into jjoms_grade (grade,lowpoint,hipoint) values('라이트',0,0);
insert into jjoms_grade (grade,lowpoint,hipoint) values('울트라라이트',1,1000);
insert into jjoms_grade (grade,lowpoint,hipoint) values('초특급슈퍼라이트',1001,3000);
insert into jjoms_grade (grade,lowpoint,hipoint) values('그레이트하이브리드라이트',3001,5000);
insert into jjoms_grade (grade,lowpoint,hipoint) values('초특급슈퍼울트라하이브리드라이트',5001,10000);


-- java_shared_service--
drop table java_shared_service
create table java_shared_service(
keyword varchar2(30)primary key,
contents varchar2(1000)not null,
info_date date not null)

--infomation--
drop table infomation;

create table infomation(
keyword_no number primary key,
keyword varchar2(30) not null,
item_no number not null,
constraint fk_java_shared_service foreign key (keyword)  references java_shared_service(keyword),
constraint fk_java_item foreign key (item_no)  references java_item(item_no))

--keywords 시퀀스(item_no)
create sequence keywords_seq;
drop sequence keywords_seq

drop table keywords;
create table keywords(
keyword_no number primary key,
keyword varchar2(30) not null,
item_no number not null,
constraint fk_java_shared_service foreign key (keyword)  references java_shared_service(keyword),
constraint fk_java_item foreign key (item_no)  references java_item(item_no))


-- 대중소 테이블 날리기 --
drop table category
drop table division
drop table section

--  대분류 --
create sequence category_no
drop sequence category_no
drop table category
create table category(
	category_no number primary key,
	java_category varchar2(30) not null
)

insert into category(category_no,java_category) values(category_no.nextval,'javaSE');
insert into category(category_no,java_category) values(category_no.nextval,'Ajax');
insert into category(category_no,java_category) values(category_no.nextval,'jQuery');
insert into category(category_no,java_category) values(category_no.nextval,'Spring');

--중분류 --
create sequence division_no
drop sequence division_no
drop table division
create table division(
	division_no number primary key,
	category_no number not null,
	java_division varchar2(30) not null,
	java_category varchar2(30) not null,
	constraint fk_category foreign key (category_no)  references category(category_no) on delete cascade
)


--소분류 --
create sequence section_no
drop sequence section_no
drop table section
create table section(
	section_no number primary key,
	division_no number not null,
	java_section varchar2(30) not null,
	java_division varchar2(30) not null,
	java_category varchar2(30) not null,
	constraint fk_division foreign key(division_no) references division(division_no) on delete cascade
)

---------------------------------------------
-- 아이템
---------------------------------------------
--item sequence
create sequence item_no_seq;
drop sequence item_no_seq;
-- item table
   drop table java_item ;
create table java_item(
   item_no number primary key,
   item_name varchar2(30) not null,
   item_price number not null,
   item_info varchar2(200) not null,
   member_id varchar2(200) not null,
   item_upload_date date not null,
   file_dir varchar2(200) not null,
   item_category varchar2(1000) not null
   );
select * from java_item;
   
    -- cart
   drop table cart;
   create table cart(
   member_id varchar2(30) not null,
   item_no number not null,
   constraint fk_member_cart foreign key(member_id) references member(member_id),
   constraint fk_item_no_cart foreign key(item_no) references java_item(item_no)
)

-- post
-- 별 평점 추가했어요~

 drop table post;
create table post(
    item_no number not null,
    item_name varchar2(100) not null,
    post_title varchar2(100) not null,
    member_id varchar2(100) not null,
    post_content varchar2(1000),
    item_star_evaluation number,
   constraint fk_member_post foreign key(member_id) references member(member_id),
   constraint fk_item_no_post foreign key(item_no) references java_item(item_no)
 )
 select * from post

 -- 카테고리 테이블 필요한지는 아직 모름

      create table item_category(
         item_no number not null,
         category varchar2(50) not null,
         constraint fk_item_no_category foreign key(item_no) references java_item(item_no)
      )
      
      drop table item_category;
   
--구매목록 리스트 테이블
drop table purchased_item_list;
<<<<<<< .mine
 select * from purchased_item_list
=======
   select * from purchased_item_list;
>>>>>>> .r354
create table purchased_item_list(
      item_no number not null,
      item_name varchar2(30) not null,
        item_price number not null,
        item_info varchar2(200) not null,
         member_id varchar2(200) not null,
         item_upload_date date not null,
         file_dir varchar2(200) not null,
         item_category varchar2(1000) not null
         
   )   
-- 쪽지 
  
   create sequence message_no_seq;
   drop sequence message_no_seq;
   
   drop table message
   
   create table message(
      message_no number primary key,
      message_sender varchar2(100) not null,
      member_id varchar2(100) not null,
      message_title varchar2(500) not null,
      message_content varchar2(1000) not null,
      sender_delete_check number default 0,
      receiver_delete_check number default 0,
      constraint fk_member_message_id foreign key(member_id) references member(member_id)
      )
   
   
-----------------------------------------------
-- qna
-----------------------------------------------
create table qna_board(
	qna_no number primary key,
	qna_title varchar2(1000) not null,
	qna_writer varchar2(100) not null,
	qna_content varchar2(4000) not null,
	qna_write_date date not null
	)
drop table qna_board;
--view_count number not null,
--ref number not null, -- 답변글묶음 번호, 원게시글 번호 
--restep number not null, -- 답변글묶음내 글순서(정렬 오름차순)
--relevel number not null -- 답변글레벨 , 답변의 단계 

drop sequence qna_board_seq;
create sequence qna_board_seq;
select * from qna_board


--------------------------------------------------------
-- 랭크
-------------------------------------------------------
-- 기존의 table 에 pk keyword 값에 정보 타이틀과 상품 제목이 겹처 무결성제약조건이 발생함으로
-- pk 값을 지웠습니다.
create table keyword_rank(
	keyword varchar2(30),
	cnt number not null,
	rank_type varchar2(10) not null
)
drop table keyword_rank

   
---------------------------------------------------------
-- 게시판
---------------------------------------------------------
drop table board;
create table board(
board_no number primary key,
board_title varchar2(50) not null,
board_time date not null,
board_content varchar2(500) not null,
member_id varchar2(20) not null,
viewcount	number not null,
ref number not null
)

create sequence board_seq;
drop sequence board_seq;

)

-- 댓글
--on delete cascade 추가했음
drop table coment;
create table coment(
coment_no number not null,
board_no number not null,
member_id varchar2(20) not null,
coment varchar2(200) not null,
coment_date date not null,
constraint fk_board foreign key(board_no) references board(board_no) on delete cascade
)

--Q&A

 create table qna_board(
	qna_no number primary key,
	qna_title varchar2(1000) not null,
	qna_writer varchar2(100) not null,
	qna_content varchar2(4000) not null,
	qna_write_date date not null,
	view_count number not null,
	ref number not null, 
	restep number not null, 
	relevel number not null 
	)
create sequence qna_board_seq;
drop table qna_board;
drop sequence qna_board_seq;
select * from qna_board
